/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataAccess;

import Entity.SalesChart;

import java.sql.Statement;

import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Home
 */
public class salesManChart {

    public static ArrayList<Entity.SalesChart> getItembyID(String itemid, String month, String year) {
        String sql = "SELECT  Items.ItemID, Items.ItemName, Sum(Quantity) AS Total, DATE FROM Items INNER JOIN BillSellDetails "
                + "ON BillSellDetails.ItemID = Items.ItemID INNER JOIN Bills "
                + "ON Bills.BillID = BillSellDetails.BillID "
                + "WHERE Items.ItemID='"+itemid+"' AND MONTH(Bills.DATE) ='"+month+"' AND YEAR(Bills.DATE)='"+year+"' "
                + "Group By Items.ItemID,Items.ItemName,DATE";

        ArrayList<Entity.SalesChart> arr = new ArrayList<SalesChart>();
        try {
            Statement stat = DBUtil.getConnection(DBUtil.getInfoDB()).createStatement();
            ResultSet rs = stat.executeQuery(sql);
            while (rs.next()) {
                Entity.SalesChart objsc = new SalesChart();
                objsc.setItemID(rs.getInt("ItemID"));
                objsc.setItemName(rs.getString("ItemName"));
                objsc.setQuantity(rs.getInt("Total"));
                objsc.setDate(rs.getString("Date"));

                arr.add(objsc);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return arr;
    }
}
